<?php

require_once('./fpdf/tcpdf/tcpdf.php');

// create new PDF document
//$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf = new TCPDF($orientation='L', $unit='mm', $format='A4', $unicode=true, $encoding='UTF-8', $diskcache=false, $pdfa=false);



/*
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Nicola Asuni');
$pdf->SetTitle('TCPDF Example 048');
$pdf->SetSubject('TCPDF Tutorial');
$pdf->SetKeywords('TCPDF, PDF, example, test, guide');
*/
// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 04', PDF_HEADER_STRING);
$pdf->SetHeaderData("", "", "รายงานการจัดเช็ค", "");

// set header and footer fonts
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setHeaderFont(Array("thsarabunb", 'B', 22));

$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor
//$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)

// ---------------------------------------------------------

// set font
//$pdf->SetFont('helvetica', 'B', 20);
$pdf->SetFont('thsarabunb','B',22);

// add a page
$pdf->AddPage();

//$pdf->Write(0, 'รายงานการจัดเช็ค', '', 0, 'L', true, 0, false, false, 0);

//$pdf->SetFont('helvetica', '', 8);

// -----------------------------------------------------------------------------


$pdf->SetFont('thsarabunb','B',16);
$tbl_header = '
<table cellspacing="0" cellpadding="1" border="1" width="100%">
<tr align="center">
	<td width="2%">#</td>
	<td width="6%">เลขที่เช็ค</td>
	<td width="8%">เลขที่ฎีกา</td>
	<td width="43%">ผู้รับเงิน</td>
	<td width="5%">งาน</td>
	<td width="5%">หมวด</td>
	<td width="5%">ประเภท</td>
	<td width="15%">จำนวนเงิน</td>
	<td width="11%">ลายมือชื่อผู้รับเงิน</td>
</tr>
';

require_once("includes/connect.php");
require_once("includes/function.php");

$db = new myDBS;

/*
$sql ="SELECT company_name, cheque_no, p_id, inpNet ".
		"FROM tbl_petition ".
		"WHERE approved_status =  'Approved' ".
		"AND cheque_no IS NOT NULL ";
*/
$from = $_REQUEST["from"];
$to = $_REQUEST["to"];

$sql ="SELECT DISTINCT cheque_no 
		FROM tbl_salary 
		WHERE approved_status =  'อนุมัติ' 
		AND cheque_no IS NOT NULL 
		AND cheque_date between '$from' and '$to' 
		UNION
		SELECT DISTINCT cheque_no 
		FROM tbl_utility 
		WHERE approved_status =  'อนุมัติ' 
		AND cheque_no IS NOT NULL 
		AND cheque_date between '$from' and '$to' 
		UNION
		SELECT DISTINCT cheque_no 
		FROM tbl_welfare 
		WHERE approved_status =  'อนุมัติ' 
		AND cheque_no IS NOT NULL 
		AND cheque_date between '$from' and '$to'
		UNION
		SELECT DISTINCT cheque_no 
		FROM tbl_petition 
		WHERE approved_status =  'Approved' 
		AND cheque_no IS NOT NULL 
		AND cheque_date between '$from' and '$to'
		ORDER BY cheque_no
		";
//echo $sql;
$result = $db->runSQL($sql);
$pdf->SetFont('thsarabunb','',16);
$i=0;

while ( $rows = $db->fetch_object ( $result )){
	$i++;

	$sql ="SELECT pet_id, expenses2_name, ex1_id, ex2_id, ex3_id, sum(salary) as salary
		FROM tbl_salary, expenses_lvl2 
		WHERE cheque_no = '".$rows->cheque_no."'
		AND ex2_id = expenses2_id
		AND approved_status =  'อนุมัติ' 
		GROUP BY pet_id, expenses2_name, ex1_id, ex2_id, ex3_id
		UNION
		SELECT pet_id, expenses2_name, ex1_id, ex2_id, ex3_id, utility
		FROM tbl_utility, expenses_lvl2 
		WHERE cheque_no = '".$rows->cheque_no."'
		AND ex2_id = expenses2_id
		AND approved_status =  'อนุมัติ' 
		UNION
		SELECT pet_id, expenses2_name, ex1_id, ex2_id, ex3_id, sum(welfare) as salary
		FROM tbl_welfare, expenses_lvl2 
		WHERE cheque_no = '".$rows->cheque_no."'
		AND ex2_id = expenses2_id
		AND approved_status =  'อนุมัติ' 
		GROUP BY pet_id, expenses2_name, ex1_id, ex2_id, ex3_id
		UNION
		SELECT DISTINCT p_id, expenses2_name, ex1_id, ex2_id, ex3_id, inpNet
		FROM tbl_petition, expenses_lvl2 
		WHERE cheque_no = '".$rows->cheque_no."'
		AND ex2_id = expenses2_id
		AND approved_status =  'Approved' 
		";
	$result1 = $db->runSQL($sql);


	$tbl_body = $tbl_body . '
		<tr>
			<td align="center">'.$i.'</td>
			<td align="center">'.$rows->cheque_no.'</td>
		
		';
		//while ( $rows1 = $db->fetch_object ( $result1 )){
		//}
		
		$j=1;
		$num_rows = $db->num_rows($result1);
		$sum = 0;
		while ( $rows1 = $db->fetch_object ( $result1 )){
			
			if (is_null($rows1->ex3_id) || empty($rows1->ex3_id) || $rows1->ex3_id ==0) {
				$ex3_id = "00";

			} else {
				$ex3_id = $rows1->ex3_id;
			}
			
			//$ex3_id = "111";
			if ($j == 1) {
				$tbl_body = $tbl_body . '
					<td align="center">&nbsp;'.$rows1->pet_id.'</td>
					<td>&nbsp;'.$rows1->expenses2_name.'</td>
					<td align="center">&nbsp;'.$rows1->ex1_id.'</td>
					<td align="center">&nbsp;'.$rows1->ex2_id.'</td>
					<td align="center">&nbsp;'.$ex3_id.'</td>
					<td align="right">&nbsp;'.number_format($rows1->salary, 2).'</td>
					<td>&nbsp;</td>
				</tr>
				';
			}
			/*
			if ($j > 1) {
				$tbl_body = $tbl_body . '
				<tr>
				<td>&nbsp;</td>
				<td>&nbsp;</td>
				<td align="center">'.$rows1->p_id.'</td>
				<td>'.$rows1->company_name.'</td>
				<td align="center">'.$rows1->ex1_id.'</td>
				<td align="center">'.$rows1->ex2_id.'</td>
				<td align="center">'.$rows1->ex3_id.'</td>
				<td align="right">'.number_format($rows1->inpNet, 2).'</td>
				<td>&nbsp;</td>
				</tr>
				';

			}
			*/
			if ($j > 1) {
				$tbl_body = $tbl_body . '
				<tr>
				<td>&nbsp;</td>
				<td>&nbsp;</td>
				<td align="center">&nbsp;'.$rows1->pet_id.'</td>
				<td>&nbsp;'.$rows1->expenses2_name.'</td>
				<td align="center">&nbsp;'.$rows1->ex1_id.'</td>
				<td align="center">&nbsp;'.$rows1->ex2_id.'</td>
				<td align="center">&nbsp;'.$ex3_id.'</td>
				<td align="right">&nbsp;'.number_format($rows1->salary, 2).'</td>
				<td>&nbsp;</td>
				</tr>
				';

			}
			$sum = $sum + $rows1->salary;
			
			
			//&& $j > 1
			if ($j == $num_rows ) {
				$tbl_body = $tbl_body . '
					<tr>
						<td colspan="7" align="right">รวม</td>
						<td align="right">'.number_format($sum, 2).'</td>
						<td>&nbsp;</td>
					</tr>	
				';
			}
			
			$j++;
		}
		

}

$tbl_footer = '</table>';

//echo $tbl_header.$tbl_body.$tbl_footer;

$pdf->writeHTML($tbl_header.$tbl_body.$tbl_footer, true, false, false, false, '');

//$pdf->Output('example_048.pdf', 'I');
$pdf->Output();

?>